package excel;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;

public class ExcelUtil {

    /**
     * 复制单元格
     */
    public static void copyCell(Workbook wb, int sheet, int fromRowindex, int fromColumindex, int toColumnindex, boolean copyValueFlag) {
        Row fromRow = getRow(wb, sheet, fromRowindex);
        Cell fromCell = fromRow.getCell(fromColumindex - 1);
        Cell newCell = fromRow.createCell(toColumnindex - 1);
        copyCell(wb, fromCell, newCell, copyValueFlag);
    }

    /**
     * 合并单元格
     *
     * @param firstcell 开始列，从1开始，框架里是从0开始，自动减1
     * @param firstrow  开始行
     * @param endcell   结束列
     * @param endrow    结束行
     */
    public static void mergedRegion(Workbook book, int sheet, int firstcell, int firstrow, int endcell, int endrow) {
        try {
            CellRangeAddress region = new CellRangeAddress(firstrow - 1, endrow - 1, firstcell - 1, endcell - 1);
            book.getSheetAt(sheet).addMergedRegion(region);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    /**
     * 获取cell
     */
    public static Cell getCell(Workbook book, int sheet, int cell, int row) {
        Cell mycell = null;
        try {
            Row myrow = book.getSheetAt(sheet).getRow(row - 1);
            if (myrow == null) {
                myrow = book.getSheetAt(sheet).createRow(row - 1);
            }
            mycell = myrow.getCell(cell - 1);
            if (mycell == null) {
                mycell = myrow.createCell(cell - 1);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return mycell;
    }


    /**
     * 获取row
     */
    public static Row getRow(Workbook book, int sheet, int row) {
        Row myrow = null;
        try {
            myrow = book.getSheetAt(sheet).getRow(row - 1);
            if (myrow == null) {
                myrow = book.getSheetAt(sheet).createRow(row - 1);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return myrow;
    }

    /**
     * 复制单元格
     *
     * @param srcCell
     * @param distCell
     * @param copyValueFlag true则连同cell的内容一起复制
     */
    public static void copyCell(Workbook wb, Cell srcCell, Cell distCell,
                                boolean copyValueFlag) {
        CellStyle newstyle = srcCell.getCellStyle();

        copyCellStyle(wb, srcCell.getCellStyle(), newstyle);
        // 样式
        distCell.setCellStyle(newstyle);
        // 评论
        if (srcCell.getCellComment() != null) {
            distCell.setCellComment(srcCell.getCellComment());
        }

        // 不同单元格数据类型处理
        // poi3.12和4.1.0区别
        // poi 3.12
        /*int srcCellType = srcCell.getCellType();
        distCell.setCellType(srcCellType);
        if (copyValueFlag) {
            if (srcCellType == Cell.CELL_TYPE_NUMERIC) {
                if (DateUtil.isCellDateFormatted(srcCell)) {
                    distCell.setCellValue(srcCell.getDateCellValue());
                } else {
                    distCell.setCellValue(srcCell.getNumericCellValue());
                }
            } else if (srcCellType == Cell.CELL_TYPE_STRING) {
                distCell.setCellValue(srcCell.getRichStringCellValue());
            } else if (srcCellType == Cell.CELL_TYPE_BLANK) {
                // nothing21
            } else if (srcCellType == Cell.CELL_TYPE_BOOLEAN) {
                distCell.setCellValue(srcCell.getBooleanCellValue());
            } else if (srcCellType == Cell.CELL_TYPE_ERROR) {
                distCell.setCellErrorValue(srcCell.getErrorCellValue());
            } else if (srcCellType == Cell.CELL_TYPE_FORMULA) {
                String cellFormula = srcCell.getCellFormula();
                System.out.println("公式：" + cellFormula);
                distCell.setCellFormula(cellFormula);
            }
        }*/

        // poi 4.1.0
        // POI判断单元格类型，以及各版本区别,3.x版本和4.x版本
        // https://blog.csdn.net/weixin_42648692/article/details/111727012
        CellType srcCellType0 = srcCell.getCellType();
        int srcCellType = srcCellType0.getCode();
        distCell.setCellType(srcCellType0);
        if (copyValueFlag) {
            if (srcCellType == CellType.NUMERIC.getCode()) {
                // 数字
                if (DateUtil.isCellDateFormatted(srcCell)) {
                    distCell.setCellValue(srcCell.getDateCellValue());
                } else {
                    distCell.setCellValue(srcCell.getNumericCellValue());
                }
            } else if (srcCellType == CellType.STRING.getCode()) {
                // 字符串
                distCell.setCellValue(srcCell.getRichStringCellValue());
            } else if (srcCellType == CellType.BLANK.getCode()) {
                // 空值
            } else if (srcCellType == CellType.BOOLEAN.getCode()) {
                // Boolean
                distCell.setCellValue(srcCell.getBooleanCellValue());
            } else if (srcCellType == CellType.ERROR.getCode()) {
                // 故障
                distCell.setCellErrorValue(srcCell.getErrorCellValue());
            } else if (srcCellType == CellType.FORMULA.getCode()) {
                // 公式
                String cellFormula = srcCell.getCellFormula();
                System.out.println("公式：" + cellFormula);
                distCell.setCellFormula(cellFormula);
            }
        }
    }

    /**
     * 复制一个单元格样式到目的单元格样式
     *
     * @param fromStyle
     * @param toStyle
     */
    private static void copyCellStyle(Workbook book, CellStyle fromStyle,
                                      CellStyle toStyle) {
        toStyle.setAlignment(fromStyle.getAlignment());
        // 边框和边框颜色
        toStyle.setBorderBottom(fromStyle.getBorderBottom());
        toStyle.setBorderLeft(fromStyle.getBorderLeft());
        toStyle.setBorderRight(fromStyle.getBorderRight());
        toStyle.setBorderTop(fromStyle.getBorderTop());
        toStyle.setTopBorderColor(fromStyle.getTopBorderColor());
        toStyle.setBottomBorderColor(fromStyle.getBottomBorderColor());
        toStyle.setRightBorderColor(fromStyle.getRightBorderColor());
        toStyle.setLeftBorderColor(fromStyle.getLeftBorderColor());

        // 背景和前景
        toStyle.setFillBackgroundColor(fromStyle.getFillBackgroundColor());
        toStyle.setFillForegroundColor(fromStyle.getFillForegroundColor());

        toStyle.setDataFormat(fromStyle.getDataFormat());
        toStyle.setFillPattern(fromStyle.getFillPattern());

        try {
            if (fromStyle instanceof XSSFCellStyle) {
                XSSFFont font = ((XSSFCellStyle) fromStyle).getFont();
                toStyle.setFont(font);
            } else {
                HSSFFont font = ((HSSFCellStyle) fromStyle).getFont(book);
                toStyle.setFont(font);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        toStyle.setHidden(fromStyle.getHidden());
        toStyle.setIndention(fromStyle.getIndention());//首行缩进
        toStyle.setLocked(fromStyle.getLocked());
        toStyle.setRotation(fromStyle.getRotation());//旋转
        toStyle.setVerticalAlignment(fromStyle.getVerticalAlignment());
        toStyle.setWrapText(fromStyle.getWrapText());
    }

    /**
     * 格子写入字符串
     */
    public static Cell setCellValueString(Workbook book, int sheet, int cell, int row, Object value) {
        Cell mycell = null;
        String v1 = "";
        if (value != null) {
            v1 = String.valueOf(value);
        }
        try {
            mycell = getCell(book, sheet, cell, row);
            String info = new String(v1.getBytes("UTF-8"));
            mycell.setCellValue(info);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return mycell;
    }

    /**
     * 格子写入日期
     */
    public static Cell setCellValueDate(Workbook book, int sheet, int cell, int row, Object value) {
        Cell mycell = null;
        String v1 = "";
        if (value != null) {
            v1 = String.valueOf(value);
        }
        try {
            mycell = getCell(book, sheet, cell, row);
            String info = new String(v1.getBytes("UTF-8"));
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
            mycell.setCellValue(sdf.parse(info));
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return mycell;
    }

    /**
     * 保存excel文件
     *
     * @param book
     * @param filepath
     */
    public static int saveExcelFile(Workbook book, String filepath) {
        try {
            File file = new File(filepath);
            if (!file.getParentFile().exists()) {
                file.getParentFile().mkdirs();
            }
            if (!file.exists()) {
                file.createNewFile();
            }
            OutputStream os = new FileOutputStream(new File(filepath));
            book.write(os);
            os.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }

        return 0;
    }

}
